/*
 * Copyright (c) 2011 Ball State University
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package edu.bsu.android.gtv.apps.bconnected.data;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Simple database access helper class for the application security. Defines the basic CRUD operations
 * for the security and starred (for "My Connections") tables, include get, insert, update and delete
 * @author Kyle Parker, University Computing Services
 */
public class DBAdapter {

    public final static String KEY_ROWID = "_id";
    public final static String KEY_UNIQUE_ID = "unique_id";
    public final static String KEY_BSUID = "bsuid";
    public final static String KEY_FIRST_NAME = "first_name";
    public final static String KEY_EXPIRATION_DATE = "expiration_date";
    private static final String DATABASE_NAME = "data";
    private static final String DATABASE_TABLE_SECURITY = "security";
    private static final int DATABASE_VERSION = 11;

    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    
    private static final String DATABASE_CREATE_SECURITY =
		"create table security (" + 
			"_id integer primary key autoincrement, " +
			"unique_id text not null, " + 
			"bsuid text not null, " + 
			"first_name text not null, " + 
			"expiration_date date not null" + 
		");";

    private final Context mCtx;

    /**
     * Database helper class to create and upgrade the database
     */
    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db)  {
            db.execSQL(DATABASE_CREATE_SECURITY);
        }
        
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_SECURITY);
            onCreate(db);
        }
    }
    
    /**
     * Constructor - takes the context to allow the database to be opened/created
     * @param ctx the Context within which to work
     */
	public DBAdapter(Context ctx) {
	    this.mCtx = ctx;
	}

    /**
     * Opens the database. If it cannot be opened, try to create a new instance of the database. If it cannot be created, throw an 
     * exception to signal the failure 
	 * @return this (self reference, allowing this to be chained in an initialization call)
	 * @throws SQLException if the database could be neither opened or created
	 */
    public DBAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    
    /**
     * Closes the database
	 */
    public void close() {
        mDbHelper.close();
    }

    /**
     * Inserts a new security record for the student. If the insert is successful, return the new rowId for the row, otherwise return
     * a -1 to indicate failure.
     * @param uniqueID - the login guid from the service handler
     * @param bsuID
     * @param firstName
     * @param expirationDate
     * @return rowId of the newly created row
     */
    public long insertSecurity(String uniqueID, String bsuID, String firstName, Date expirationDate) {
    	DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
    
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_UNIQUE_ID, uniqueID);
        initialValues.put(KEY_BSUID, bsuID);
        initialValues.put(KEY_FIRST_NAME, firstName);
        initialValues.put(KEY_EXPIRATION_DATE, df.format(expirationDate));

        return mDb.insert(DATABASE_TABLE_SECURITY, null, initialValues);
    }

    /**
     * Deletes the security context with the given rowId
     * @param rowID
     * @return true|false depending on whether or not the row was found
     */
    public boolean deleteSecurity(long rowID) {
        return mDb.delete(DATABASE_TABLE_SECURITY, KEY_ROWID + "=" + rowID, null) > 0;
    }
    
    /**
     * Deletes all security rows in the database
     * @return true|false depending on whether or not any rows were deleted
     */
    public boolean deleteSecurityAll() {
    	// NOTE: This does not need to reference a specific BSU ID because the app can only have 1 simultaneous user at a time
    	// So when a student logs out, there will only be one record in the table - delete it and add a new row with an expiration date
        return mDb.delete(DATABASE_TABLE_SECURITY, null, null) > 0;
    }
    
    /**
     * Returns a cursor containing the most recent security context
     * @return mCursor containing the most recent login for the student
     */
    public Cursor getSecurityMostRecent() {
        Cursor mCursor = null;
        
        try {
        	mCursor = mDb.query(true, DATABASE_TABLE_SECURITY, new String[] {KEY_ROWID, KEY_UNIQUE_ID, KEY_BSUID, KEY_FIRST_NAME, 
        			KEY_EXPIRATION_DATE}, null, null, null, null, KEY_EXPIRATION_DATE + " DESC", "1");
        
	        if (mCursor != null) {
	            mCursor.moveToFirst();
	        }
        } catch (Exception ex) {
        	ex.printStackTrace();
        }
        
        return mCursor;
    }
    
    /**
     * Returns a cursor positioned at the security context that matches the given BSU ID
     * @param bsuID
     * @return mCursor containing the security context for a specific student
     */
    public Cursor getSecurityByBSUID(String bsuID) {
        Cursor mCursor =
    		mDb.query(true, DATABASE_TABLE_SECURITY, new String[] {KEY_ROWID, KEY_BSUID, KEY_FIRST_NAME, KEY_EXPIRATION_DATE}, 
    				KEY_BSUID + "= '" + bsuID + "'", null, null, null, null, null);
        
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        
        return mCursor;
    }
    
    /**
     * Updates the record using the details provided. The record to be updated is specified using the rowId.
     * @param rowID
     * @param uniqueID
     * @param bsuID
     * @param expirationDate
     * @return true|false depending on whether a row was found to update
     */
    public boolean updateSecurity(long rowID, String uniqueID, String bsuID, Date expirationDate) {
    	DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
        
        ContentValues args = new ContentValues();
        args.put(KEY_UNIQUE_ID, uniqueID);
        args.put(KEY_BSUID, bsuID);
        args.put(KEY_EXPIRATION_DATE, df.format(expirationDate));

        return mDb.update(DATABASE_TABLE_SECURITY, args, KEY_ROWID + "=" + rowID, null) > 0;
    }
    
    /**
     * Updates the record using the details provided. The record to be updated is specified using the rowId.
     * @param rowID
     * @param uniqueID
     * @param bsuID
     * @return true|false depending on whether a row was found to update
     */
    public boolean updateSecurity(long rowID, String uniqueID, String bsuID) {        
        ContentValues args = new ContentValues();
        args.put(KEY_UNIQUE_ID, uniqueID);
        args.put(KEY_BSUID, bsuID);

        return mDb.update(DATABASE_TABLE_SECURITY, args, KEY_ROWID + "=" + rowID, null) > 0;
    }
}
